package zuoye;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.mysql.jdbc.Connection;

import util.DBUtil;

public class SubjectDao implements ISubject {
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.ISubject#findALL()
	 */
	@Override
	public List<Subject> findALL() {
		Connection con = (Connection) DBUtil.getConnection();
		String sql = "select * from s_subject";
		List<Subject> list = new ArrayList<Subject>();
		PreparedStatement pre = null;
		ResultSet rs = null;
		try {
			pre = con.prepareStatement(sql);
			rs = pre.executeQuery();
			while (rs.next()) {
				Subject s = new Subject();
				s.setId(rs.getLong("id"));
				s.setSubjectname(rs.getString("sujectname"));
				s.setClasshour(rs.getLong("classhor"));
				s.setGradeid(rs.getLong("gradeid"));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, pre, con);
		}
		return list;
	}

	// 增加方法
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.ISubject#add(zuoye.Subject)
	 */
	@Override
	public int add(Subject s) {
		Connection con = (Connection) DBUtil.getConnection();
		String sql = "insert into s_subject(subjectname,classhour,gradeid) values(?,?,?)";
		PreparedStatement pre = null;
		int r = 0;
		Date d = new Date();
		try {
			pre = con.prepareStatement(sql);
			pre.setString(1, s.getSubjectname());
			pre.setLong(2, s.getClasshour());
			pre.setLong(3, s.getGradeid());
			r = pre.executeUpdate();
			System.out.println("增加了" + r + "数据");
		} catch (SQLException e1) {
			e1.printStackTrace();
		} finally {
			DBUtil.close(null, pre, con);
		}
		return 0;
	}

	// 删除方法
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.ISubject#delete(zuoye.Subject)
	 */
	@Override
	public int delete(Subject s) {
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;
		try {
			String sql = "delete  from s_subject where id= ?";
			st = con.prepareStatement(sql);
			st.setLong(1, s.getId());

			return st.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}

		return 0;
	}

	// 修改方法
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.ISubject#update(zuoye.Subject)
	 */
	@Override
	public int update(Subject s) {
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;

		try {
			// 用占位符
			String sql = "UPDATE s_student set sname=?,telephone=?,qq=?,wechat=?,fromcity=?,address=?,sex=?,gradeid=?,enterdate=?,birthday=? where stuno=?";
			st = con.prepareStatement(sql);

			st.setString(1, s.getSubjectname());
			st.setLong(2, s.getClasshour());
			st.setLong(3, s.getGradeid());
			return st.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBUtil.close(null, st, con);
		}
		return 0;
	}

	// 分页方法
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.ISubject#paging(int, int)
	 */
	@Override
	public List<Subject> paging(int pageNo, int pageSize) {

		String sql = "select * from s_subject order by id asc limit ?,?";
		List<Subject> list = new ArrayList<>();
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		try {
			st = con.prepareStatement(sql);
			st.setInt(1, (pageNo - 1) * pageSize);
			st.setInt(2, pageSize);
			rs = st.executeQuery();
			while (rs.next()) {
				Subject s = new Subject();
				s.setId(rs.getLong("id"));
				s.setSubjectname(rs.getString("sujectname"));
				s.setClasshour(rs.getLong("classhor"));
				s.setGradeid(rs.getLong("gradeid"));
				list.add(s);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return list;

	}

	// 统计
	/*
	 * (non-Javadoc)
	 * 
	 * @see zuoye.ISubject#total()
	 */
	@Override
	public int total() {
		Connection con = (Connection) DBUtil.getConnection();
		PreparedStatement st = null;
		ResultSet rs = null;
		String sql = "select count(*)  from s_subject";
		int total = 0;
		try {
			st = con.prepareStatement(sql);
			rs = st.executeQuery();

			if (rs.next()) {
				total = rs.getInt(1);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBUtil.close(rs, st, con);
		}
		return total;
	}
}
